package persistence.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;

import core.exception.SaveException;

import persistence.DataPersistor;
import persistence.ReportDAO;

/**
 * ReportDAO for database persistence
 * @author Justin
 *
 */
public class DBReportDAO implements ReportDAO {

	private DataPersistor _DP;
	private DBTransactionManager _TM;
	/**
	 * Constructor
	 * @throws Exception 
	 */
	public DBReportDAO() {
		_DP = DataPersistor.instance();
		_TM = (DBTransactionManager)_DP.getTransactionManager();
	}
	
	/**
	 * @inheritDoc
	 */
	@Override
	public void setRemovedItemsTime(Calendar cal) throws SaveException {
		// TODO What if it's not in the DB yet?
		PreparedStatement stmt = null;
		String query;
		if(removedItemsReportExistsinDB()){
			query = "UPDATE Reports SET Name = ?, LastDateRan = ? WHERE ID=?";
		}
		else{
			query = "INSERT INTO Reports (Name, LastDateRan, ID) VALUES(?, ?, ?);";
		}
		try{
			stmt = _TM.getConnection().prepareStatement(query);
			stmt.setString(1, "RemovedItemsReport");
			java.sql.Date lastRun = new java.sql.Date(cal.getTime().getTime());
			stmt.setDate(2, lastRun);
			stmt.setInt(3, 1);
			if(stmt.executeUpdate() != 1){
				throw new SaveException("SQLite: Couldn't save removed items report");
			}
		} catch (SQLException e){
			throw new SaveException("SQLite: Couldn't save removed items report");
		}
		finally {
			try {
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}
	
	private boolean removedItemsReportExistsinDB() throws SaveException{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String query = "SELECT COUNT(*) FROM Reports WHERE Name = ?";
		try {
			stmt = _TM.getConnection().prepareStatement(query);
			stmt.setString(1, "RemovedItemsReport");
			rs = stmt.executeQuery();
			//if it has nothing, return false
			rs.next();
			int num = rs.getInt(1);
			if(num == 0)return false;
			//return true
			return true;
		} catch(SQLException e){
			throw new SaveException("SQLite: Couldn't get whether removed items report exists");
		}
		finally {
			try {
				if(rs != null) rs.close();
				if(stmt != null) stmt.close();
			}
			catch (SQLException e) {
				//ignore
			}
		}
	}

}
